package com.example.studentmanagement2.repository.JdbcRepository;

import com.example.studentmanagement2.model.Student;
import com.example.studentmanagement2.repository.Repository.StudentRepository;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;

@Repository
public class JdbcStudentRepository implements StudentRepository {

    private final JdbcTemplate jdbcTemplate;


    public JdbcStudentRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<Student> findAll() {
        return jdbcTemplate.query(
                "SELECT * FROM students",
                (rs, rowNum) -> new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("class_name")
                )
        );
    }

    @Override
    public Optional<Student> findById(String id) {
        try {
            return jdbcTemplate.queryForObject(
                    "SELECT * FROM students WHERE id = ?",
                    new Object[]{id},
                    (rs, rowNum) -> Optional.of(new Student(
                            rs.getString("id"),
                            rs.getString("name"),
                            rs.getString("class_name")
                    ))
            );
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty(); // 显式处理空结果
        }
    }

    @Override
    public Student save(Student student) {
        jdbcTemplate.update(
                "INSERT INTO students (id, name, class_name) VALUES (?, ?, ?)",
                student.getId(), student.getName(), student.getClassName()
        );
        return student;
    }

    @Override
    public void update(Student student, String originalId) {  // 新增参数 originalId

        jdbcTemplate.update(

                "UPDATE students SET id=?, name=?, class_name=? WHERE id=?",
                student.getId(),     // 新学号
                student.getName(),
                student.getClassName(),
                originalId          // 原始学号（WHERE 条件）
        );
    }

    @Override
    public void deleteById(String id) {
        jdbcTemplate.update("DELETE FROM students WHERE id = ?", id);
    }

    @Override
    public boolean existsById(String id) {
        String sql = "SELECT COUNT(*) FROM students WHERE id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return count != null && count > 0;
    }
    @Override
    public List<Student> search(String keyword) {
        String sql = "SELECT * FROM students WHERE " +
                "id LIKE ? OR name LIKE ? OR class_name LIKE ?";
        String pattern = "%" + keyword + "%";
        return jdbcTemplate.query(
                sql,
                new Object[]{pattern, pattern, pattern},
                (rs, rowNum) -> new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("class_name")
                )
        );
    }
    @Override
    public List<String> findDistinctClasses() {
        String sql = "SELECT DISTINCT class_name FROM students";
        return jdbcTemplate.queryForList(sql, String.class);
    }





}